Using CASE with Data Modifying Statements

What Are SQL's Data Modifying Statements?

There are three data modifying statements in SQL. They are called data modifying because they make changes to the data that is stored within a database table. These statements are quite familiar, even to novice SQL users. They are:

INSERT – Adds records to a table

UPDATE – Changes values in table columns

DELETE – Removes records from the table

SELECT brand, model,

          CASE brand

               WHEN 'Ford' THEN 'Founder was Henry Ford'

               WHEN 'Opel' THEN 'Founder was Adam Opel'

               WHEN 'Toyota' THEN 'Founder was Kiichiro Toyoda'

                ELSE '-'

        END AS info

INTO tab

FROM car;

Using CASE with UPDATE

UPDATE car SET info =

CASE brand

          WHEN 'Ford' THEN 'Founder was Henry Ford'

          WHEN 'Toyota' THEN 'Founder was Kiichiro Toyoda'

          ELSE '-'

          END

;

Deleting Data Using the CASE Expression

DELETE FROM car

WHERE FALSE = CASE

    WHEN info IS NOT NULL THEN TRUE

      ELSE FALSE

END

;

SELECT d1.dept_name,

d1.budget,

total.total_salaries,

d1.budget - total.total_salaries AS budget_after_salaries

FROM ( SELECT department_id, SUM(salary) AS total_salaries

  FROM employee

GROUP BY department_id

) AS total

INNER JOIN    department d1 ON d1.department_id = total.department_id